import copy
import json
import logging
import re
from concurrent.futures import ThreadPoolExecutor, as_completed
from datetime import datetime, timedelta

from product_upload.domain.basic.basic_gg import BasicGg
from product_upload.domain.basic.basic_product import BasicProduct
from product_upload.util.basic.mysql_util import db_list_by_page, db_get_one, db_batch_update, db_batch_insert
from product_upload.util.basic.openai_util import model_request

logger = logging.getLogger(__name__)


def filter_table_data(html_content):
    if html_content is None or html_content == "":
        return ""
    # 正则表达式模式用于匹配任何标签内的文本内容
    cleaned_text = re.sub(r'<[^>]+>', '', html_content)
    non_ascii_pattern = re.compile(r'[^\x00-\x7F]+')
    # 使用正则表达式移除所有的非ASCII字符
    cleaned_text_without_unicode = non_ascii_pattern.sub('', cleaned_text)
    return cleaned_text_without_unicode.strip()


def get_numeric_value(product, key, default=1):
    value = product.get(key)
    if value is None or not isinstance(value, (int, float)):
        try:
            value = float(value)
        except (ValueError, TypeError):
            return default
    return value if isinstance(value, (int, float)) else default


def dict_to_custom_string(d):
    if isinstance(d, str):
        return d
    elif isinstance(d, dict):
        parts = []
        for key, value in d.items():
            if isinstance(value, list):
                value_str = ','.join(map(str, value))
            elif isinstance(value, dict):
                value_str = dict_to_custom_string(value)  # 递归处理字典
            else:
                value_str = str(value)
            parts.append(f"{key}:{value_str}")
        return '@&@'.join(parts)
    else:
        return str(d)


# 转换爬取网页接口的数据为我期望的数据格式
def transform_data(original_json):
    original = json.loads(original_json).get('data', None)
    if not original:
        return None
    product_info = original.get('product_info', None)
    if not product_info:
        return None
    # 放入供应商ID和名字
    seller_info = original.get('seller_info', None)
    supplier_code = ""
    supplier_name = ""
    region = "US"
    if seller_info:
        supplier_code = seller_info.get('store_code')
        supplier_name = seller_info.get('store_name')
    product_info_dict = {
        "sku": product_info.get("sku"),
        "region": region,
        "supplier_code": supplier_code,
        "supplier_name": supplier_name,
        "weightUnit": "lbs.",
        "lengthUnit": "in",
        "comboFlag": product_info.get("combo_flag", False),
        "title": product_info.get("product_name", ""),
        "description": filter_table_data(product_info.get("description", "")),
        "characteristics": filter_table_data(product_info.get("characteristic", "")),
        "images_list": [item["popup"].split("?")[0] for item in product_info.get("image_list", [])],
        "not_available": "",
        "is_ltl": False
    }
    if product_info.get("ltl_info", {}):
        product_info_dict["is_ltl"] = product_info.get("ltl_info", {}).get("is_ltl", False)
    if product_info.get("non_sellable_on_full_list", []) is not None:
        product_info_dict["not_available"] = ",".join([x.get("platform_name", "") for x in product_info.get("non_sellable_on_full_list", [])])
    specification = product_info.get("specification", None)

    if specification:
        property_infos = specification.get("property_infos", [])
        product_info_dict["properties"] = ",".join([f'{x["property_name"]}:{x["property_value_name"]}' for x in property_infos])
        package_size = specification.get("package_size", None)
        if package_size:
            if product_info_dict["comboFlag"]:
                # 获取组合包裹尺寸
                product_info_dict["length"] = get_numeric_value(package_size.get("combo")[0], "length")
                product_info_dict["width"] = get_numeric_value(package_size.get("combo")[0], "width")
                product_info_dict["height"] = get_numeric_value(package_size.get("combo")[0], "height")
                product_info_dict["weight"] = get_numeric_value(package_size.get("combo")[0], "weight")
            else:
                # 获取普通包裹尺寸
                product_info_dict["length"] = get_numeric_value(package_size.get("general"), "length")
                product_info_dict["width"] = get_numeric_value(package_size.get("general"), "width")
                product_info_dict["height"] = get_numeric_value(package_size.get("general"), "height")
                product_info_dict["weight"] = get_numeric_value(package_size.get("general"), "weight")
            product_info_dict["packages"] = [product_info_dict["length"], product_info_dict["width"], product_info_dict["height"], product_info_dict["weight"]]

        product_dimensions = specification.get("product_dimensions", None)
        if product_dimensions:
            assembled_info = product_dimensions.get("assemble_info", {})
            product_info_dict["assembledLength"] = get_numeric_value(assembled_info, "length_show")
            product_info_dict["assembledWidth"] = get_numeric_value(assembled_info, "width_show")
            product_info_dict["assembledHeight"] = get_numeric_value(assembled_info, "height_show")
            product_info_dict["assembledWeight"] = get_numeric_value(assembled_info, "weight_show")
            product_info_dict["dimensions"] = [product_info_dict["assembledLength"], product_info_dict["assembledWidth"], product_info_dict["assembledHeight"], product_info_dict["assembledWeight"]]

    return product_info_dict


def refresh_gg_quantity_and_price():
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "开始更新GG库存和价格,也许含有新品.....")
    for page in range(1, 99999):
        basic_gg_res = db_list_by_page("basic_gg", ",".join(BasicGg.fields), "status = 2", None, page, 1000)
        if len(basic_gg_res) == 0:
            break
        need_update_gg = []
        need_insert_basic = []
        need_update_price = []
        for row in basic_gg_res:
            # RPA新拉取的商品
            if row[3] is None or row[3] == "":
                product_info = transform_data(row[4])
                price_info = json.loads(row[5])
                # 构造追加到basic_product数据
                main_img = product_info.get('images_list')[0] if len(product_info.get('images_list', [])) > 0 else ""
                published = 0
                if price_info.get("price") > 0 and price_info.get("total_amount") > 0:
                    published = 1
                origin = copy.deepcopy(product_info)
                del origin["not_available"]
                del origin["dimensions"]
                del origin["packages"]
                del origin["images_list"]
                origin = dict_to_custom_string(origin)
                origin = origin.replace("@&@", "\n").replace("\n\n\n", "\n").replace("\n\n", "\n")
                json_text = json.dumps(product_info)
                price = price_info.get('price') + price_info.get("total_amount")
                dimensions = product_info["dimensions"]
                packages = product_info["packages"]
                dimensions_str = f'{dimensions[0]} * {dimensions[1]} * {dimensions[2]} * {dimensions[3]}'
                packages_str = f'{packages[0]} * {packages[1]} * {packages[2]} * {packages[3]}'
                basic_item = [0, published, product_info.get('not_available'), "GG", product_info.get('sku'), main_img, product_info.get('title', ""), dimensions_str, packages_str, price, price_info.get('quantity'), json.dumps(json_text),
                              origin]
                need_update_gg.append([row[0], 1, product_info.get('sku'), json.dumps(product_info)])
                need_insert_basic.append(basic_item)
            else:  # 更新价格和库存
                price_info = json.loads(row[5])
                quantity = price_info.get('quantity')
                published = 0
                if price_info.get("price") > 0 and price_info.get("total_amount") > 0:
                    published = 1
                price = price_info.get('price') + price_info.get("total_amount")
                basic_prod = db_get_one("basic_product", f"platform='GG' and sku='{row[3]}'")
                need_update_price.append([basic_prod[0], published, quantity, price])
        if len(need_update_gg) > 0:
            db_batch_update("basic_gg", ["id", "status", "sku", "origin_info"], need_update_gg)
        if len(need_insert_basic) > 0:
            db_batch_insert("basic_product", ["status", "published", "not_available", "platform", "sku", "main_image", "title", "dimensions", "packages", "price", "quantity", "json_text", "product_info"], need_insert_basic)
        if len(need_update_price) > 0:
            db_batch_update("basic_product", ["id", "published", "quantity", "price"], need_update_price)
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), "更新GG库存和价格完成,也许还有新品.....已产生txt文件.....")


def ai_create_title(product_info):
    sys_prompt = """
    I will provide you with product information, please extract keywords as titles based on this information. In order to facilitate better retrieval, the title length should not exceed 10 words. The return value is in JSON format.
For example: {"new_title":"your output new  title"}
    """
    new_title = model_request(True, product_info, sys_prompt, model="gpt-4o-mini").get("new_title", "")
    if not new_title:
        # new_title = model_request(True, product_info, sys_prompt, model="gemini-2.0-flash-001").get("new_title", "")
        new_title = model_request(True, product_info, sys_prompt, model="gpt-4o-mini").get("new_title", "")
    return new_title


def basic_gg_transform_to_basic_product():
    one_week_ago = datetime.now() - timedelta(weeks=2)
    create_time = one_week_ago.strftime("%Y-%m-%d %H:%M:%S")
    basic_product_list = db_list_by_page("basic_product", "id,not_available,product_info", f'published = 1 and product_type ="" and create_time <= "{create_time}" and status != 2', BasicProduct, 1, 10000)
    logger.info(f'start basic_product still not product_type, ai rename title...count:{len(basic_product_list)}')
    if basic_product_list:
        with ThreadPoolExecutor(max_workers=10) as executor:
            futures = []
            if basic_product_list:
                for page in range(0, len(basic_product_list), len(basic_product_list) // 10 + 1):
                    futures.append(executor.submit(thread_rename_title, basic_product_list))
                for future in as_completed(futures):
                    try:
                        future.result()
                    except Exception as e:
                        logger.error(f"basic_product rename : {e}")
                logger.info(f'success end ai rename title...')
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # 此处开始basic_gg开始追加
    basic_gg_list = db_list_by_page("basic_gg", ",".join(BasicGg.fields), 'status is null or sku is null or sku =""', BasicGg, 1, 50000)
    need_update_gg = []
    need_insert_basic = []
    if basic_gg_list:
        logger.info("start basic_gg to basic_product...")
        for basic_gg in basic_gg_list:
            product_info_dict = transform_data(basic_gg.origin_info)
            if not product_info_dict:
                continue
            # 构造追加到basic_product数据
            main_img = product_info_dict.get('images_list')[0] if len(product_info_dict.get('images_list', [])) > 0 else ""
            origin = copy.deepcopy(product_info_dict)
            del origin["not_available"]
            del origin["dimensions"]
            del origin["packages"]
            del origin["images_list"]
            link = f'https://www.gigab2b.com/index.php?route=product/product&product_id={basic_gg.product_id}'
            origin = dict_to_custom_string(origin)
            origin = origin.replace("@&@", "\n").replace("\n\n\n", "\n").replace("\n\n", "\n")

            need_update_gg.append([basic_gg.id, 1, product_info_dict.get('sku'), json.dumps(product_info_dict)])
            sku = product_info_dict.get('sku', None)
            if not sku:
                continue
            if db_get_one("basic_product", f"platform='GG' and sku='{sku}'", BasicProduct):
                continue
            dimensions = product_info_dict["dimensions"]
            packages = product_info_dict["packages"]
            dimensions_str = f'{dimensions[0]} * {dimensions[1]} * {dimensions[2]} * {dimensions[3]}'
            packages_str = f'{packages[0]} * {packages[1]} * {packages[2]} * {packages[3]}'
            ltl = '{"delivery":"LTL"}' if product_info_dict.get("is_ltl", False) else ""
            basic_item = [0, 0, product_info_dict.get('not_available'), "GG", sku, main_img, product_info_dict.get('title', ""), dimensions_str, packages_str, json.dumps(product_info_dict), origin, create_time, link,
                          product_info_dict.get("region"), product_info_dict.get("supplier_code"), product_info_dict.get("supplier_name"), ltl]
            need_insert_basic.append(basic_item)
    if len(need_update_gg) > 0:
        db_batch_update("basic_gg", ["id", "status", "sku", "origin_info"], need_update_gg)
    if len(need_insert_basic) > 0:
        db_batch_insert("basic_product", ["status", "published", "not_available", "platform", "sku", "main_image", "title", "dimensions", "packages", "json_text", "product_info", "create_time", "link", "region", "supplier_code", "supplier_name", "extra_field"], need_insert_basic)
        logger.info("success end basic_gg to basic_product...")


def thread_rename_title(prod_list):
    for tmp_basic_info in prod_list:
        if tmp_basic_info.product_info:
            new_title = ai_create_title(f'product_info:\n{tmp_basic_info.product_info}')
            if new_title:
                db_batch_update("basic_product", ["id", "title", 'remark'], [[tmp_basic_info.id, new_title, 'ai rename']])


if __name__ == '__main__':
    basic_gg_transform_to_basic_product()
    pass
